package com.robot.member.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.robot.member.pojo.Member;
import org.springframework.data.jpa.repository.Query;

/**
 * member数据访问接口
 *
 * @author wuyunbin
 */
public interface MemberDao extends JpaRepository<Member, String>, JpaSpecificationExecutor<Member> {
    /**
     * 通过手机号查询用户
     *
     * @param phone
     * @return
     */
    Member findByPhone(String phone);

    /**
     * 通过openId查询用户
     *
     * @param openId
     * @return
     */
    public Member findByOpenId(String openId);

    /**
     * 通过recommendId查询用户列表并分页
     *
     * @param recommendId
     * @param pageable
     * @return
     */
    public Page<Member> findAllByRecommendIdOrderByCreatedAtDesc(String recommendId, Pageable pageable);

    /**
     * 通过ID查找会员的推荐人名字
     *
     * @param recommendId
     * @return
     */
    @Query(value = "select nickname from member.member where id=?", nativeQuery = true)
    public String findRecommenderName(String recommendId);

    /**
     * 通过ID查找会员的推荐人数
     *
     * @param id
     * @return
     */
    @Query(value = "select count(*) from member.member where recommend_id=?", nativeQuery = true)
    public Integer findRecommendedNumber(String id);

    /**
     * 通过id和status查找会员
     *
     * @param id
     * @param status
     * @return
     */
    public Member findByIdAndStatus(String id, String status);


    /**
     * 通过推荐人名字查询推荐列表
     *
     * @param nickname
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where recommend_id=(select id from member where nickname=?) and deleted_at is null order by created_at desc", nativeQuery = true)
    public Page<Member> findAllByRecommendName(String nickname, Pageable pageable);

    /**
     * 通过推荐人手机号查询推荐列表
     *
     * @param phone
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where recommend_id=(select id from member where phone=?) and deleted_at is null order by created_at desc", nativeQuery = true)
    public Page<Member> findAllByRecommendPhone(String phone, Pageable pageable);


    /**
     * 根据isPlus查找今日会员
     *
     * @param isPlus
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where is_plus=? and TO_DAYS(NOW()) - TO_DAYS(created_at) =0", nativeQuery = true)
    public Page<Member> findAllByIsPlusAndToday(String isPlus, Pageable pageable);

    /**
     * 根据isPlus查找昨天会员
     *
     * @param isPlus
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where is_plus=? and TO_DAYS(NOW()) - TO_DAYS(created_at) =1", nativeQuery = true)
    public Page<Member> findAllByIsPlusAndYesterday(String isPlus, Pageable pageable);

    /**
     * 根据isPlus查找最近7日会员
     *
     * @param isPlus
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where is_plus=? and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(created_at)", nativeQuery = true)
    public Page<Member> findAllByIsPlusAndWeek(String isPlus, Pageable pageable);

    /**
     * 根据isPlus和时间段查找总会员
     *
     * @param isPlus
     * @param startTime
     * @param endTime
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where is_plus=? and created_at between ? and ?", nativeQuery = true)
    public Page<Member> findAllByIsPlusAndBetweenAnd(String isPlus, String startTime, String endTime, Pageable pageable);

    /**
     * 根据isPlus查找总会员
     *
     * @param isPlus
     * @param pageable
     * @return
     */
    @Query(value = "select * from member.member where is_plus=? ", nativeQuery = true)
    public Page<Member> findAllByIsPlusAndTotal(String isPlus, Pageable pageable);
}
